package com.apigen.platform.service.impl;

import com.apigen.platform.dto.DatabaseConnectionRequest;
import com.apigen.platform.service.DatabaseMetadataService;
import com.apigen.platform.util.DatabaseTypeMapper;
import com.apigen.platform.vo.DatabaseTableVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库元数据服务实现类
 *
 * @author API Generator Platform
 * @since 2025-10-03
 */
@Slf4j
@Service
public class DatabaseMetadataServiceImpl implements DatabaseMetadataService {

    @Override
    public Connection createConnection(DatabaseConnectionRequest connectionRequest) throws SQLException {
        String jdbcUrl = buildJdbcUrl(connectionRequest);
        log.info("正在连接数据库: {}", jdbcUrl);
        
        Properties props = new Properties();
        props.setProperty("user", connectionRequest.getUsername());
        if (StringUtils.hasText(connectionRequest.getPassword())) {
            props.setProperty("password", connectionRequest.getPassword());
        }
        
        // 设置连接超时
        if (connectionRequest.getTimeout() != null && connectionRequest.getTimeout() > 0) {
            DriverManager.setLoginTimeout(connectionRequest.getTimeout());
        }
        
        // 根据数据库类型设置特定属性
        setDatabaseSpecificProperties(connectionRequest, props);
        
        return DriverManager.getConnection(jdbcUrl, props);
    }

    @Override
    public Map<String, Object> testConnection(DatabaseConnectionRequest connectionRequest) {
        Map<String, Object> result = new HashMap<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            if (connection != null && !connection.isClosed()) {
                DatabaseMetaData metaData = connection.getMetaData();
                
                result.put("success", true);
                result.put("message", "数据库连接成功");
                result.put("databaseProductName", metaData.getDatabaseProductName());
                result.put("databaseProductVersion", metaData.getDatabaseProductVersion());
                result.put("driverName", metaData.getDriverName());
                result.put("driverVersion", metaData.getDriverVersion());
                result.put("url", metaData.getURL());
                result.put("userName", metaData.getUserName());
                
                log.info("数据库连接测试成功: {} - {}", 
                    metaData.getDatabaseProductName(), 
                    metaData.getDatabaseProductVersion());
            } else {
                result.put("success", false);
                result.put("message", "数据库连接失败");
            }
        } catch (SQLException e) {
            log.error("数据库连接测试失败: {}", e.getMessage());
            result.put("success", false);
            result.put("message", "数据库连接失败: " + e.getMessage());
            result.put("errorCode", e.getErrorCode());
            result.put("sqlState", e.getSQLState());
        }
        
        return result;
    }

    @Override
    public List<DatabaseTableVO> getAllTables(DatabaseConnectionRequest connectionRequest) {
        List<DatabaseTableVO> tables = new ArrayList<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 获取表信息
            String[] types = {"TABLE"};
            try (ResultSet rs = metaData.getTables(connectionRequest.getDatabase(), null, "%", types)) {
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    
                    // 应用表名过滤
                    if (!isTableMatched(tableName, connectionRequest.getTableFilter())) {
                        continue;
                    }
                    
                    DatabaseTableVO table = DatabaseTableVO.builder()
                            .tableName(tableName)
                            .tableComment(rs.getString("REMARKS"))
                            .tableType(rs.getString("TABLE_TYPE"))
                            .build();
                    
                    // 获取表的额外信息（MySQL特有）
                    if ("MYSQL".equalsIgnoreCase(connectionRequest.getDatabaseType())) {
                        enrichMySQLTableInfo(connection, table);
                    }
                    
                    // 获取字段数量
                    table.setFieldCount(getFieldCount(connection, tableName));
                    
                    tables.add(table);
                }
            }
            
            log.info("成功获取到 {} 个数据表", tables.size());
        } catch (SQLException e) {
            log.error("获取数据库表列表失败: {}", e.getMessage());
            throw new RuntimeException("获取数据库表列表失败: " + e.getMessage(), e);
        }
        
        return tables;
    }

    @Override
    public DatabaseTableVO getTableDetail(DatabaseConnectionRequest connectionRequest, String tableName) {
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 获取表基本信息
            DatabaseTableVO table = null;
            try (ResultSet rs = metaData.getTables(connectionRequest.getDatabase(), null, tableName, new String[]{"TABLE"})) {
                if (rs.next()) {
                    table = DatabaseTableVO.builder()
                            .tableName(rs.getString("TABLE_NAME"))
                            .tableComment(rs.getString("REMARKS"))
                            .tableType(rs.getString("TABLE_TYPE"))
                            .build();
                }
            }
            
            if (table == null) {
                throw new RuntimeException("表 " + tableName + " 不存在");
            }
            
            // 获取字段信息
            List<DatabaseTableVO.DatabaseFieldVO> fields = getTableFields(connectionRequest, tableName);
            table.setFields(fields);
            table.setFieldCount(fields.size());
            
            // 获取索引信息
            List<DatabaseTableVO.DatabaseIndexVO> indexes = getTableIndexes(connectionRequest, tableName);
            table.setIndexes(indexes);
            table.setIndexCount(indexes.size());
            
            // 获取MySQL特有的表信息
            if ("MYSQL".equalsIgnoreCase(connectionRequest.getDatabaseType())) {
                enrichMySQLTableInfo(connection, table);
            }
            
            return table;
        } catch (SQLException e) {
            log.error("获取表详细信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表详细信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public List<DatabaseTableVO.DatabaseFieldVO> getTableFields(DatabaseConnectionRequest connectionRequest, String tableName) {
        List<DatabaseTableVO.DatabaseFieldVO> fields = new ArrayList<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 获取主键信息
            Set<String> primaryKeys = new HashSet<>(getTablePrimaryKeys(connectionRequest, tableName));
            
            // 获取字段信息
            try (ResultSet rs = metaData.getColumns(connectionRequest.getDatabase(), null, tableName, "%")) {
                while (rs.next()) {
                    String columnName = rs.getString("COLUMN_NAME");
                    String dataType = rs.getString("TYPE_NAME");
                    int columnSize = rs.getInt("COLUMN_SIZE");
                    int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                    boolean nullable = rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
                    String defaultValue = rs.getString("COLUMN_DEF");
                    String remarks = rs.getString("REMARKS");
                    boolean autoIncrement = "YES".equals(rs.getString("IS_AUTOINCREMENT"));
                    
                    DatabaseTableVO.DatabaseFieldVO field = DatabaseTableVO.DatabaseFieldVO.builder()
                            .fieldName(columnName)
                            .fieldType(dataType)
                            .fieldLength(columnSize)
                            .fieldScale(decimalDigits)
                            .isNullable(nullable)
                            .isPrimaryKey(primaryKeys.contains(columnName))
                            .isAutoIncrement(autoIncrement)
                            .defaultValue(defaultValue)
                            .fieldComment(remarks)
                            .javaType(DatabaseTypeMapper.mapToJavaType(dataType, columnSize, decimalDigits))
                            .jdbcType(DatabaseTypeMapper.mapToJdbcType(dataType))
                            .build();
                    
                    fields.add(field);
                }
            }
            
            // 按字段顺序排序
            fields.sort(Comparator.comparing(f -> f.getSortOrder() != null ? f.getSortOrder() : 0));
            
        } catch (SQLException e) {
            log.error("获取表字段信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表字段信息失败: " + e.getMessage(), e);
        }
        
        return fields;
    }

    @Override
    public List<DatabaseTableVO.DatabaseIndexVO> getTableIndexes(DatabaseConnectionRequest connectionRequest, String tableName) {
        List<DatabaseTableVO.DatabaseIndexVO> indexes = new ArrayList<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            Map<String, DatabaseTableVO.DatabaseIndexVO> indexMap = new HashMap<>();
            
            try (ResultSet rs = metaData.getIndexInfo(connectionRequest.getDatabase(), null, tableName, false, false)) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    if (indexName == null || "PRIMARY".equals(indexName)) {
                        continue; // 跳过主键索引
                    }
                    
                    boolean unique = !rs.getBoolean("NON_UNIQUE");
                    String columnName = rs.getString("COLUMN_NAME");
                    
                    DatabaseTableVO.DatabaseIndexVO index = indexMap.computeIfAbsent(indexName, k -> 
                        DatabaseTableVO.DatabaseIndexVO.builder()
                                .indexName(indexName)
                                .isUnique(unique)
                                .indexType(unique ? "UNIQUE" : "NORMAL")
                                .indexColumns("")
                                .build()
                    );
                    
                    // 添加字段到索引列
                    String currentColumns = index.getIndexColumns();
                    if (StringUtils.hasText(currentColumns)) {
                        index.setIndexColumns(currentColumns + "," + columnName);
                    } else {
                        index.setIndexColumns(columnName);
                    }
                }
            }
            
            indexes.addAll(indexMap.values());
            
        } catch (SQLException e) {
            log.error("获取表索引信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表索引信息失败: " + e.getMessage(), e);
        }
        
        return indexes;
    }

    @Override
    public List<String> getTablePrimaryKeys(DatabaseConnectionRequest connectionRequest, String tableName) {
        List<String> primaryKeys = new ArrayList<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            try (ResultSet rs = metaData.getPrimaryKeys(connectionRequest.getDatabase(), null, tableName)) {
                while (rs.next()) {
                    primaryKeys.add(rs.getString("COLUMN_NAME"));
                }
            }
            
        } catch (SQLException e) {
            log.error("获取表主键信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表主键信息失败: " + e.getMessage(), e);
        }
        
        return primaryKeys;
    }

    @Override
    public List<Map<String, Object>> getTableForeignKeys(DatabaseConnectionRequest connectionRequest, String tableName) {
        List<Map<String, Object>> foreignKeys = new ArrayList<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            try (ResultSet rs = metaData.getImportedKeys(connectionRequest.getDatabase(), null, tableName)) {
                while (rs.next()) {
                    Map<String, Object> fk = new HashMap<>();
                    fk.put("fkName", rs.getString("FK_NAME"));
                    fk.put("fkColumnName", rs.getString("FKCOLUMN_NAME"));
                    fk.put("pkTableName", rs.getString("PKTABLE_NAME"));
                    fk.put("pkColumnName", rs.getString("PKCOLUMN_NAME"));
                    fk.put("updateRule", rs.getInt("UPDATE_RULE"));
                    fk.put("deleteRule", rs.getInt("DELETE_RULE"));
                    foreignKeys.add(fk);
                }
            }
            
        } catch (SQLException e) {
            log.error("获取表外键信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表外键信息失败: " + e.getMessage(), e);
        }
        
        return foreignKeys;
    }

    @Override
    public Map<String, Object> getDatabaseInfo(DatabaseConnectionRequest connectionRequest) {
        Map<String, Object> info = new HashMap<>();
        
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            info.put("databaseProductName", metaData.getDatabaseProductName());
            info.put("databaseProductVersion", metaData.getDatabaseProductVersion());
            info.put("driverName", metaData.getDriverName());
            info.put("driverVersion", metaData.getDriverVersion());
            info.put("maxConnections", metaData.getMaxConnections());
            info.put("maxTableNameLength", metaData.getMaxTableNameLength());
            info.put("maxColumnNameLength", metaData.getMaxColumnNameLength());
            info.put("supportsBatchUpdates", metaData.supportsBatchUpdates());
            info.put("supportsTransactions", metaData.supportsTransactions());
            
        } catch (SQLException e) {
            log.error("获取数据库信息失败: {}", e.getMessage());
            throw new RuntimeException("获取数据库信息失败: " + e.getMessage(), e);
        }
        
        return info;
    }

    @Override
    public String buildJdbcUrl(DatabaseConnectionRequest connectionRequest) {
        StringBuilder url = new StringBuilder();
        
        switch (connectionRequest.getDatabaseType().toUpperCase()) {
            case "MYSQL":
                url.append("jdbc:mysql://")
                   .append(connectionRequest.getHost())
                   .append(":")
                   .append(connectionRequest.getPort())
                   .append("/")
                   .append(connectionRequest.getDatabase())
                   .append("?useUnicode=true&characterEncoding=utf8")
                   .append("&serverTimezone=Asia/Shanghai")
                   .append("&allowPublicKeyRetrieval=true");
                
                if (connectionRequest.getUseSSL() != null) {
                    url.append("&useSSL=").append(connectionRequest.getUseSSL());
                } else {
                    url.append("&useSSL=false");
                }
                break;
                
            case "POSTGRESQL":
                url.append("jdbc:postgresql://")
                   .append(connectionRequest.getHost())
                   .append(":")
                   .append(connectionRequest.getPort())
                   .append("/")
                   .append(connectionRequest.getDatabase());
                break;
                
            case "ORACLE":
                url.append("jdbc:oracle:thin:@")
                   .append(connectionRequest.getHost())
                   .append(":")
                   .append(connectionRequest.getPort())
                   .append(":")
                   .append(connectionRequest.getDatabase());
                break;
                
            case "SQL_SERVER":
                url.append("jdbc:sqlserver://")
                   .append(connectionRequest.getHost())
                   .append(":")
                   .append(connectionRequest.getPort())
                   .append(";databaseName=")
                   .append(connectionRequest.getDatabase());
                break;
                
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + connectionRequest.getDatabaseType());
        }
        
        // 添加自定义连接参数
        if (StringUtils.hasText(connectionRequest.getConnectionParams())) {
            if (url.toString().contains("?")) {
                url.append("&");
            } else {
                url.append("?");
            }
            url.append(connectionRequest.getConnectionParams());
        }
        
        return url.toString();
    }

    /**
     * 设置数据库特定的连接属性
     */
    private void setDatabaseSpecificProperties(DatabaseConnectionRequest connectionRequest, Properties props) {
        switch (connectionRequest.getDatabaseType().toUpperCase()) {
            case "MYSQL":
                props.setProperty("useInformationSchema", "true");
                props.setProperty("remarks", "true");
                props.setProperty("useSSL", String.valueOf(connectionRequest.getUseSSL()));
                break;
            case "POSTGRESQL":
                props.setProperty("remarksReporting", "true");
                break;
            case "ORACLE":
                props.setProperty("remarksReporting", "true");
                break;
        }
    }

    /**
     * 检查表名是否匹配过滤条件
     */
    private boolean isTableMatched(String tableName, String tableFilter) {
        if (!StringUtils.hasText(tableFilter)) {
            return true;
        }
        
        String[] filters = tableFilter.split(",");
        for (String filter : filters) {
            filter = filter.trim();
            if (filter.contains("*")) {
                // 支持通配符匹配
                String regex = filter.replace("*", ".*");
                if (tableName.matches(regex)) {
                    return true;
                }
            } else {
                // 精确匹配
                if (tableName.equals(filter)) {
                    return true;
                }
            }
        }
        
        return false;
    }

    /**
     * 获取字段数量
     */
    private Integer getFieldCount(Connection connection, String tableName) {
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            try (ResultSet rs = metaData.getColumns(null, null, tableName, "%")) {
                int count = 0;
                while (rs.next()) {
                    count++;
                }
                return count;
            }
        } catch (SQLException e) {
            log.warn("获取表 {} 字段数量失败: {}", tableName, e.getMessage());
            return 0;
        }
    }

    /**
     * 丰富MySQL表信息
     */
    private void enrichMySQLTableInfo(Connection connection, DatabaseTableVO table) {
        String sql = "SELECT ENGINE, TABLE_COLLATION, AUTO_INCREMENT, TABLE_ROWS, " +
                    "ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024), 2) AS TABLE_SIZE_KB " +
                    "FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, connection.getCatalog());
            ps.setString(2, table.getTableName());
            
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    table.setEngine(rs.getString("ENGINE"));
                    table.setCollation(rs.getString("TABLE_COLLATION"));
                    table.setAutoIncrement(rs.getLong("AUTO_INCREMENT"));
                    table.setDataRows(rs.getLong("TABLE_ROWS"));
                    table.setTableSize(rs.getLong("TABLE_SIZE_KB"));
                }
            }
        } catch (SQLException e) {
            log.warn("获取MySQL表 {} 额外信息失败: {}", table.getTableName(), e.getMessage());
        }
    }
}
